﻿using System.Data;
using System.Data.SQLite;
using System.Runtime.InteropServices;

namespace MySQLite
{
    /// <summary>
    /// SQLite数据库操作帮助类
    /// </summary>
    public class MySQLiteHelper
    {
        #region 实例化

        /// <summary>
        /// 私有构造，杜绝直接new类
        /// </summary>
        private static readonly object obj = new object();
        private static readonly object sqlObj = new object();

        /// <summary>
        /// 私有成员，使用时分配内存 
        /// </summary>
        private static MySQLiteHelper _singInstance;

        /// <summary>
        /// 获取实例
        /// </summary>
        public static MySQLiteHelper Instance
        {
            get
            {
                if (_singInstance == null)
                {
                    lock (obj)
                    {
                        if (_singInstance == null)
                        {
                            _singInstance = new MySQLiteHelper();
                        }
                    }
                }
                return _singInstance;
            }
        }

        /// <summary>
        /// 初始化实例
        /// </summary>
        public MySQLiteHelper()
        {
            if (string.IsNullOrEmpty(Connection))
            {
                if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
                {
                    Connection = $"Data Source={AppDomain.CurrentDomain.BaseDirectory}\\RENY_Collect.db;Pooling=true;FailIfMissing=false;Journal Mode=WAL";
                }
                else
                {
                    Connection = $"Data Source={AppDomain.CurrentDomain.BaseDirectory}/RENY_Collect.db";
                }
            }
            MyConnection = new SQLiteConnection(Connection);
        }

        #endregion 

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string Connection { get; set; }

        /// <summary>
        /// 创建数据库连接类的对象
        /// </summary>
        public SQLiteConnection MyConnection { get; set; }

        /// <summary>
        /// 打开数据库
        /// </summary>
        public void Open()
        {
            try
            {
                MyConnection.Open();
            }
            catch (Exception ex)
            {
                var error = $"【打开数据库错误】{ex.Message}";
                //throw new Exception(error);
            }
        }

        /// <summary>
        /// 执行单个sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>执行成功返回true 失败返回false</returns>
        public bool ExecuteSql(string sql)
        {
            bool flag = false;
            while (true)
            {
                lock (sqlObj)
                {
                    try
                    {
                        if (LockExecuteSql(sql))
                        {
                            flag = true;
                            break;
                        }
                    }
                    catch (Exception ex)
                    {
                        if (ex.Message.Contains("database is locked"))
                        {
                            System.Threading.Thread.Sleep(10);
                            continue;
                        }
                        var error = $"【执行sql语句失败】{sql}{ex.Message}";
                        break;
                        //throw new Exception(error);
                    }
                }
            }
            return flag;
        }

        /// <summary>
        /// 锁定sql执行
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>执行成功返回true 失败返回false</returns>
        private bool LockExecuteSql(string sql)
        {
            bool flag = false;
            lock (sqlObj)
            {
                using (SQLiteConnection conn = new SQLiteConnection(Connection))
                {
                    try
                    {
                        conn.Open();
                        SQLiteCommand command = new SQLiteCommand(sql, conn);
                        command.ExecuteNonQuery();
                        command.Parameters.Clear();
                        flag = true;
                    }
                    catch (Exception ex)
                    {
                        flag = false;
                        var error = $"【执行sql语句失败】{sql}{ex.Message}";
                        throw new Exception(error);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return flag;
        }

        /// <summary>
        /// 执行单个sql语句获取 int
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>执行成功返回具体数量 失败返回-1</returns>
        public int ExecuteSqlToInt(string sql)
        {
            int count = -1;
            lock (sqlObj)
            {
                using (SQLiteConnection conn = new SQLiteConnection(Connection))
                {
                    try
                    {
                        DataTable dt = new DataTable();
                        conn.Open();
                        SQLiteCommand command = new SQLiteCommand(sql, conn);
                        SQLiteDataAdapter sqlDA = new SQLiteDataAdapter(command);
                        sqlDA.Fill(dt);
                        if (!string.IsNullOrEmpty(dt.Rows[0][0]?.ToString()))
                        {
                            count = Convert.ToInt32(dt.Rows[0][0]);
                        }
                        else
                        {
                            return 0;
                        }
                    }
                    catch (Exception ex)
                    {
                        var error = $"【查询数据错误】{sql}{ex.Message}";
                        //throw new Exception(error);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return count;
        }

        /// <summary>
        /// 执行单个sql语句获取 int
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>执行成功返回具体数量 失败返回-1</returns>
        public string ExecuteSqlToString(string sql)
        {
            string result = string.Empty;
            lock (sqlObj)
            {
                using (SQLiteConnection conn = new SQLiteConnection(Connection))
                {
                    try
                    {
                        DataTable dt = new DataTable();
                        conn.Open();
                        SQLiteCommand command = new SQLiteCommand(sql, conn);
                        SQLiteDataAdapter sqlDA = new SQLiteDataAdapter(command);
                        sqlDA.Fill(dt);
                        var rowValue = dt.Rows[0][0]?.ToString();
                        if (!string.IsNullOrEmpty(rowValue))
                        {
                            result = rowValue;
                        }
                        else
                        {
                            return string.Empty;
                        }
                    }
                    catch (Exception ex)
                    {
                        var error = $"【查询数据错误】{sql}{ex.Message}";
                        //throw new Exception(error);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="guid"></param>
        /// <param name="fieldName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public void ExecuteWriteBlob(string tableName, string guid, string fieldName, byte[] value)
        {
            lock (sqlObj)
            {
                using (SQLiteConnection conn = new SQLiteConnection(Connection))
                {
                    try
                    {
                        conn.Open();
                        // 插入BLOB数据
                        using (var command = new SQLiteCommand(conn))
                        {
                            command.CommandText = $" Update {tableName} set {fieldName} =@data where Guid = '{guid}' ";
                            command.Parameters.AddWithValue("@data", value);
                            command.ExecuteNonQuery();
                        }
                    }
                    catch (Exception ex)
                    {
                        var error = $"【写入Blob数据错误】{ex.Message}";
                        //throw new Exception(error);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="guid"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public byte[]? ExecuteReadBlob(string tableName, string guid, string fieldName)
        {
            lock (sqlObj)
            {
                using (SQLiteConnection conn = new SQLiteConnection(Connection))
                {
                    try
                    {
                        conn.Open();
                        // 读取BLOB数据
                        using (var command = new SQLiteCommand(conn))
                        {
                            command.CommandText = $"SELECT {fieldName} FROM {tableName} WHERE Guid = '{guid}' ";
                            using (var reader = command.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    try
                                    {
                                        byte[] blobData = reader.GetFieldValue<byte[]>(0);
                                        return blobData;
                                    }
                                    catch
                                    {
                                    }
                                    return null;
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        var error = $"【写入Blob数据错误】{ex.Message}";
                        //throw new Exception(error);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return null;
        }

        /// <summary>
        /// 执行单个sql语句获取 DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回DataTable</returns>
        public DataTable ExecuteSqlForDataTable(string sql)
        {
            DataTable dt = new DataTable();
            lock (sqlObj)
            {
                using (SQLiteConnection conn = new SQLiteConnection(Connection))
                {
                    try
                    {
                        conn.Open();
                        SQLiteCommand command = new SQLiteCommand(sql, conn);
                        SQLiteDataAdapter sqlDA = new SQLiteDataAdapter(command);
                        sqlDA.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        var error = $"【查询数据错误】{sql}{ex.Message}";
                        //throw new Exception(error);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 事务执行，执行一组sql语句
        /// </summary>
        /// <param name="sqls">sql语句集合</param>
        /// <returns>成功返回true 失败返回false</returns>
        public bool ExecuteSqlsByTran(List<string> sqls)
        {
            bool flag = false;
            lock (sqlObj)
            {
                using (SQLiteConnection conn = new SQLiteConnection(Connection))
                {
                    conn.Open();
                    SQLiteTransaction tran = conn.BeginTransaction();
                    SQLiteCommand command = new SQLiteCommand
                    {
                        Connection = conn,
                        Transaction = tran
                    };
                    try
                    {
                        foreach (string sql in sqls)
                        {
                            command.CommandText = sql;
                            command.ExecuteNonQuery();
                        }
                        tran.Commit();
                        flag = true;
                    }
                    catch (SQLiteException ex)
                    {
                        tran.Rollback();
                        flag = false;
                        var error = $"【执行sql事物失败】{ex.Message}";
                        //throw new Exception(error);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return flag;
        }

        /// <summary>
        /// 清空数据库
        /// </summary>
        public void ExecuteClearData()
        {
            using (SQLiteConnection conn = new SQLiteConnection(Connection))
            {
                conn.Open();
                SQLiteCommand command = new SQLiteCommand
                {
                    Connection = conn
                };
                try
                {
                    command.CommandText = "VACUUM";
                    command.ExecuteNonQuery();
                }
                catch (SQLiteException ex)
                {
                    var error = $"【执行sql事物失败】{ex.Message}";
                    //throw new Exception(error);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            try
            {
                MyConnection.Close();
            }
            catch (Exception ex)
            {
                var error = $"【关闭数据库连接失败】{ex.Message}";
                //throw new Exception(error);
            }
        }
    }
}
